package com.utbm.lo54.dao.mysql;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.utbm.lo54.dao.ClientDAOInterface;
import com.utbm.lo54.entity.Client;
import com.utbm.lo54.entity.Course;
import com.utbm.lo54.entity.Session;

public class MysqlClientDAO implements ClientDAOInterface{

	private DataSource dataSource;

	public MysqlClientDAO() {
		super();
		try {
			Context namingContext = new InitialContext();
			dataSource = (DataSource) namingContext.lookup("java:comp/env/jdbc/testDatasource");
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	@Override
	public void delete(Client cl) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			Statement stmt = connection.createStatement();
			stmt.executeQuery("DELETE FROM COURSE.CLIENT WHERE ID_CLIENT =" + cl.getId());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	@Override
	public Client create(Client cl) {
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("INSERT INTO COURSE.CLIENT(FIRSTNAME_CLIENT,LASTNAME_CLIENT,ADDRESS_CLIENT,PHONE_CLIENT,EMAIL_CLIENT,ID_SESSION) VALUES (?,?,?,?,?,?)",new String[]{"ID_CLIENT"});
			stmt.setString(1, cl.getFirstName());
			stmt.setString(2, cl.getLastName());
			stmt.setString(3, cl.getAddress());
			stmt.setString(4, cl.getPhone());
			stmt.setString(5, cl.getEmail());
			stmt.setLong(6, cl.getSessionId());
			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs.next()) {
				cl.setId(rs.getLong(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return cl;
	}

	@Override
	public Client findById(Long id) {
		Connection connection = null;
		Client cl = new Client();
		try {
			connection = dataSource.getConnection();
			Statement stmt = connection.createStatement();
			ResultSet set = stmt.executeQuery("SELECT FIRSTNAME_CLIENT,LASTNAME_CLIENT,ADDRESS_CLIENT,PHONE_CLIENT,EMAIL_CLIENT FROM COURSE.CLIENT WHERE ID_CLIENT = "+ id );
			if(set.next()){
				cl.setId(id);
				cl.setSessionId(set.getLong(1));
				cl.setFirstName(set.getString(2));
				cl.setLastName(set.getString(3));
				cl.setAddress(set.getString(4));
				cl.setPhone(set.getString(5));
				cl.setEmail(set.getString(6));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cl;
	}

	@Override
	public void update(Client cl) {
		// TODO Auto-generated method stub

	}

	@Override
	public Set<Client> getAllClient() {
		Connection connection = null;
		Set<Client> list = new HashSet<Client>();
		try {
			connection = dataSource.getConnection();
			PreparedStatement stmt = (PreparedStatement) connection.prepareStatement("Select ID_SESSION,FIRSTNAME_CLIENT,LASTNAME_CLIENT,ADDRESS_CLIENT,PHONE_CLIENT,EMAIL_CLIENT,ID_CLIENT FROM COURSE.CLIENT");


			ResultSet set = stmt.executeQuery();
			while(set.next()){
				Client cl = new Client();
				cl.setSessionId(set.getLong(1));
				cl.setFirstName(set.getString(2));
				cl.setLastName(set.getString(3));
				cl.setAddress(set.getString(4));
				cl.setPhone(set.getString(5));
				cl.setEmail(set.getString(6));
				cl.setId(set.getLong(7));
				list.add(cl);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {

				e.printStackTrace();
			}
		}
		return list;
	}




}
